﻿using System;
using System.Configuration;
using System.Data;

using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using Microsoft.Reporting.WebForms;

namespace dcwj.report
{
    public partial class dbbgdata : System.Web.UI.Page
    {
        private String M_CONSTR = ConfigurationManager.ConnectionStrings["dcwjConnectionString"].ToString();
        protected void Page_Load(object sender, EventArgs e)
        {           
            if (!IsPostBack)
            {
                Database objDB = new SqlDatabase(M_CONSTR);
                int n = Int32.Parse(Request["paperid"].ToString());
                string pTitle = objDB.ExecuteScalar(CommandType.Text, @"SELECT PaperName  FROM Table_PAPER where paperid=" + n.ToString()).ToString();
                tt.Text = pTitle;
                lblPaperNm.Text = pTitle;
                Label1.Text = getval(n, 1, 1, false);
                Label2.Text = getval(n, 1, 1, true);
                Label3.Text = getval(n, 1, 2, false);
                Label4.Text = getval(n, 1, 2, true);
                Label5.Text = getval(n, 1, 3, false);
                Label6.Text = getval(n, 1, 3, true);
                Label7.Text = getval(n, 1, 4, false);
                Label8.Text = getval(n, 1, 4, true);
                Label9.Text = getval(n, 1, 5, false);
                Label10.Text = getval(n, 1, 5, true);

                Label11.Text = getval(n, 2, 1, false);
                Label12.Text = getval(n, 2, 1, true);
                Label13.Text = getval(n, 2, 2, false);
                Label14.Text = getval(n, 2, 2, true);
                Label15.Text = getval(n, 2, 3, false);
                Label16.Text = getval(n, 2, 3, true);
                Label17.Text = getval(n, 2, 4, false);
                Label18.Text = getval(n, 2, 4, true);
                Label19.Text = getval(n, 2, 5, false);
                Label20.Text = getval(n, 2, 5, true);

                Label21.Text = getval(n, 3, 1, false);
                Label22.Text = getval(n, 3, 1, true);
                Label23.Text = getval(n, 3, 2, false);
                Label24.Text = getval(n, 3, 2, true);
                Label25.Text = getval(n, 3, 3, false);
                Label26.Text = getval(n, 3, 3, true);
                Label27.Text = getval(n, 3, 4, false);
                Label28.Text = getval(n, 3, 4, true);
                Label29.Text = getval(n, 3, 5, false);
                Label30.Text = getval(n, 3, 5, true);

                Label31.Text = getval(n, 4, 1, false);
                Label32.Text = getval(n, 4, 1, true);
                Label33.Text = getval(n, 4, 2, false);
                Label34.Text = getval(n, 4, 2, true);
                Label35.Text = getval(n, 4, 3, false);
                Label36.Text = getval(n, 4, 3, true);
                Label37.Text = getval(n, 4, 4, false);
                Label38.Text = getval(n, 4, 4, true);
                Label39.Text = getval(n, 4, 5, false);
                Label40.Text = getval(n, 4, 5, true);

                Label41.Text = getval(n, 5, 1, false);
                Label42.Text = getval(n, 5, 1, true);
                Label43.Text = getval(n, 5, 2, false);
                Label44.Text = getval(n, 5, 2, true);
                Label45.Text = getval(n, 5, 3, false);
                Label46.Text = getval(n, 5, 3, true);
                Label47.Text = getval(n, 5, 4, false);
                Label48.Text = getval(n, 5, 4, true);
                Label49.Text = getval(n, 5, 5, false);
                Label50.Text = getval(n, 5, 5, true);

                Label51.Text = getval(n, 6, 1, false);
                Label52.Text = getval(n, 6, 1, true);
                Label53.Text = getval(n, 6, 2, false);
                Label54.Text = getval(n, 6, 2, true);
                Label55.Text = getval(n, 6, 3, false);
                Label56.Text = getval(n, 6, 3, true);
                Label57.Text = getval(n, 6, 4, false);
                Label58.Text = getval(n, 6, 4, true);
                Label59.Text = getval(n, 6, 5, false);
                Label60.Text = getval(n, 6, 5, true);

                Label61.Text = getval(n, 7, 1, false);
                Label62.Text = getval(n, 7, 1, true);
                Label63.Text = getval(n, 7, 2, false);
                Label64.Text = getval(n, 7, 2, true);
                Label65.Text = getval(n, 7, 3, false);
                Label66.Text = getval(n, 7, 3, true);
                Label67.Text = getval(n, 7, 4, false);
                Label68.Text = getval(n, 7, 4, true);
                Label69.Text = getval(n, 7, 5, false);
                Label70.Text = getval(n, 7, 5, true);

                Label71.Text = getval(n, 8, 1, false);
                Label72.Text = getval(n, 8, 1, true);
                Label73.Text = getval(n, 8, 2, false);
                Label74.Text = getval(n, 8, 2, true);
                Label75.Text = getval(n, 8, 3, false);
                Label76.Text = getval(n, 8, 3, true);
                Label77.Text = getval(n, 8, 4, false);
                Label78.Text = getval(n, 8, 4, true);
                Label79.Text = getval(n, 8, 5, false);
                Label80.Text = getval(n, 8, 5, true);

                Label81.Text = getval(n, 9, 1, false);
                Label82.Text = getval(n, 9, 1, true);
                Label83.Text = getval(n, 9, 2, false);
                Label84.Text = getval(n, 9, 2, true);
                Label85.Text = getval(n, 9, 3, false);
                Label86.Text = getval(n, 9, 3, true);
                Label87.Text = getval(n, 9, 4, false);
                Label88.Text = getval(n, 9, 4, true);
                Label89.Text = getval(n, 9, 5, false);
                Label90.Text = getval(n, 9, 5, true);

                Label91.Text = getval(n, 10, 1, false);
                Label92.Text = getval(n, 10, 1, true);
                Label93.Text = getval(n, 10, 2, false);
                Label94.Text = getval(n, 10, 2, true);
                Label95.Text = getval(n, 10, 3, false);
                Label96.Text = getval(n, 10, 3, true);
                Label97.Text = getval(n, 10, 4, false);
                Label98.Text = getval(n, 10, 4, true);
                Label99.Text = getval(n, 10, 5, false);
                Label100.Text = getval(n, 10, 5, true);

                Label101.Text = getval(n, 11, 1, false);
                Label102.Text = getval(n, 11, 1, true);
                Label103.Text = getval(n, 11, 2, false);
                Label104.Text = getval(n, 11, 2, true);
                Label105.Text = getval(n, 11, 3, false);
                Label106.Text = getval(n, 11, 3, true);
                Label107.Text = getval(n, 11, 4, false);
                Label108.Text = getval(n, 11, 4, true);
                Label109.Text = getval(n, 11, 5, false);
                Label110.Text = getval(n, 11, 5, true);

                Label111.Text = getval(n, 12, 1, false);
                Label112.Text = getval(n, 12, 1, true);
                Label113.Text = getval(n, 12, 2, false);
                Label114.Text = getval(n, 12, 2, true);
                Label115.Text = getval(n, 12, 3, false);
                Label116.Text = getval(n, 12, 3, true);
                Label117.Text = getval(n, 12, 4, false);
                Label118.Text = getval(n, 12, 4, true);
                Label119.Text = getval(n, 12, 5, false);
                Label120.Text = getval(n, 12, 5, true);

                Label121.Text = getval(n, 13, 1, false);
                Label122.Text = getval(n, 13, 1, true);
                Label123.Text = getval(n, 13, 2, false);
                Label124.Text = getval(n, 13, 2, true);
                Label125.Text = getval(n, 13, 3, false);
                Label126.Text = getval(n, 13, 3, true);
                Label127.Text = getval(n, 13, 4, false);
                Label128.Text = getval(n, 13, 4, true);
                Label129.Text = getval(n, 13, 5, false);
                Label130.Text = getval(n, 13, 5, true);

                Label131.Text = getval(n, 14, 1, false);
                Label132.Text = getval(n, 14, 1, true);
                Label133.Text = getval(n, 14, 2, false);
                Label134.Text = getval(n, 14, 2, true);
                Label135.Text = getval(n, 14, 3, false);
                Label136.Text = getval(n, 14, 3, true);
                Label137.Text = getval(n, 14, 4, false);
                Label138.Text = getval(n, 14, 4, true);
                Label139.Text = getval(n, 14, 5, false);
                Label140.Text = getval(n, 14, 5, true);

                Label141.Text = getval(n, 15, 1, false);
                Label142.Text = getval(n, 15, 1, true);
                Label143.Text = getval(n, 15, 2, false);
                Label144.Text = getval(n, 15, 2, true);
                Label145.Text = getval(n, 15, 3, false);
                Label146.Text = getval(n, 15, 3, true);
                Label147.Text = getval(n, 15, 4, false);
                Label148.Text = getval(n, 15, 4, true);
                Label149.Text = getval(n, 15, 5, false);
                Label150.Text = getval(n, 15, 5, true);

                Label151.Text = getval(n, 16, 1, false);
                Label152.Text = getval(n, 16, 1, true);
                Label153.Text = getval(n, 16, 2, false);
                Label154.Text = getval(n, 16, 2, true);
                Label155.Text = getval(n, 16, 3, false);
                Label156.Text = getval(n, 16, 3, true);
                Label157.Text = getval(n, 16, 4, false);
                Label158.Text = getval(n, 16, 4, true);
                Label159.Text = getval(n, 16, 5, false);
                Label160.Text = getval(n, 16, 5, true);

                Label161.Text = getval(n, 17, 1, false);
                Label162.Text = getval(n, 17, 1, true);
                Label163.Text = getval(n, 17, 2, false);
                Label164.Text = getval(n, 17, 2, true);
                Label165.Text = getval(n, 17, 3, false);
                Label166.Text = getval(n, 17, 3, true);
                Label167.Text = getval(n, 17, 4, false);
                Label168.Text = getval(n, 17, 4, true);
                Label169.Text = getval(n, 17, 5, false);
                Label170.Text = getval(n, 17, 5, true);

                DataSet ds = objDB.ExecuteDataSet(CommandType.Text, @"SELECT row_number() over (order by uid) as rowNm
      ,[Q1Answer]
      ,[Q2Answer]
      ,[Q3Answer]
      ,[Q4Answer]
      ,[Q5Answer]
      ,[Q6Answer]
      ,[Q7Answer]
      ,[Q8Answer]
      ,[Q9Answer]
      ,[Q10Answer]
      ,[Q11Answer]
      ,[Q12Answer]
      ,[Q13Answer]
      ,[Q14Answer]
      ,[Q15Answer]
      ,[Q16Answer]
      ,[Q17Answer]
      ,[QSum]
  FROM [Table_sumary]
where pid=" + n.ToString());
                dcwjDataSet_detail dsd = new dcwjDataSet_detail();
                if (ds != null && ds.Tables[0].Rows.Count > 0)
                {                    
                    for(int i=0;i<ds.Tables[0].Rows.Count;i++)
                    {
                        dsd.dtable.AdddtableRow(
                            ds.Tables[0].Rows[i][0].ToString(),
                            ds.Tables[0].Rows[i][1].ToString(),
                            ds.Tables[0].Rows[i][2].ToString(),
                            ds.Tables[0].Rows[i][3].ToString(),
                            ds.Tables[0].Rows[i][4].ToString(),
                            ds.Tables[0].Rows[i][5].ToString(),
                            ds.Tables[0].Rows[i][6].ToString(),
                            ds.Tables[0].Rows[i][7].ToString(),
                            ds.Tables[0].Rows[i][8].ToString(),
                            ds.Tables[0].Rows[i][9].ToString(),
                            ds.Tables[0].Rows[i][10].ToString(),
                            ds.Tables[0].Rows[i][11].ToString(),
                            ds.Tables[0].Rows[i][12].ToString(),
                            ds.Tables[0].Rows[i][13].ToString(),
                            ds.Tables[0].Rows[i][14].ToString(),
                            ds.Tables[0].Rows[i][15].ToString(),
                            ds.Tables[0].Rows[i][16].ToString(),
                            ds.Tables[0].Rows[i][17].ToString(),
                            ds.Tables[0].Rows[i][18].ToString());
                    }                    
                }

                DataSet dsInfo = objDB.ExecuteDataSet(CommandType.Text, @"SELECT 
PaperName as 问卷名称,
(select BrandName from Table_BRAND where BrandID = p.BrandID) as 品牌,
(select PMNM from Table_PM where PMID = p.PMID) as 项目经理,
(select PTYPENM from Table_PTYPE where PTYPEID = p.PTYPEID) as 项目类型,
(select TeacherName from Table_TEACHER where TeacherID = p.TeacherID) as 讲师,
TrainingDays as 培训天数,
(select PositionName from Table_POSITION where PositionID = p.PositionID) as 岗位


FROM Table_PAPER p where p.PaperID="+n.ToString());
                if (dsInfo != null && dsInfo.Tables[0].Rows.Count > 0)
                {
                    for (int i = 0; i < dsInfo.Tables[0].Rows.Count; i++)
                    {
                        dsd.PaperInfo.AddPaperInfoRow(
                            dsInfo.Tables[0].Rows[i][0].ToString(),
                            dsInfo.Tables[0].Rows[i][1].ToString(),
                            dsInfo.Tables[0].Rows[i][2].ToString(),
                            dsInfo.Tables[0].Rows[i][3].ToString(),
                            dsInfo.Tables[0].Rows[i][4].ToString(),
                            dsInfo.Tables[0].Rows[i][5].ToString(),
                            dsInfo.Tables[0].Rows[i][6].ToString());
                    }
                }

                ReportDataSource rds0 = new ReportDataSource("ds_sumary", (DataTable)dsd.dtable);
                ReportDataSource rds1 = new ReportDataSource("ds_pInfo", (DataTable)dsd.PaperInfo);

                ReportViewer1.LocalReport.DataSources.Add(rds0);
                ReportViewer1.LocalReport.DataSources.Add(rds1);

                ReportViewer1.LocalReport.Refresh();
            }
        }

        private string getval(int pid, int qid, int qAnswer, bool bper)
        {
            Database objDB = new SqlDatabase(M_CONSTR);
            string ret = string.Empty;
            if (true == bper)//求百分比
            {
                Object o1 = objDB.ExecuteScalar(CommandType.Text, @"select count(*) from Table_RESULT where paperid=" + pid.ToString() + " and Qanswer=" + qAnswer.ToString() + " and qid =" + qid.ToString());
                Object o2 = objDB.ExecuteScalar(CommandType.Text, @"select count(*) from Table_RESULT where paperid=" + pid.ToString() + " and qid =" + qid.ToString());
                if (o1 != null && o2 != null)
                {
                    int n1 = Int32.Parse(o1.ToString());
                    int n2 = Int32.Parse(o2.ToString());

                    return ((double)n1 / n2).ToString("P");
                }
                else
                {
                    return string.Empty;
                }
            }
            else//求个数
            {
                Object o = objDB.ExecuteScalar(CommandType.Text, @"select count(*) from Table_RESULT where paperid=" + pid.ToString() + " and Qanswer=" + qAnswer.ToString() + " and qid =" + qid.ToString());
                if (o != null)
                {
                    return o.ToString();
                }
                else
                {
                    return string.Empty;
                }
            }
        }
    }
}